package com.project.dao.sys;

import java.util.List;

import org.hibernate.SQLQuery;
import org.springframework.stereotype.Repository;

import com.project.common.Constants;
import com.project.dao.BaseDao;
import com.project.entity.sys.Area;

/**
 * 区域Dao
 * @author
 *
 */
@Repository
public class AreaDao extends BaseDao<Area>{

	public List<Area> getProvinceList(){
		String hql = "From Area where parentcode is null and status='"+Constants.STATUS_OPEN+"' ORDER BY id";
		return super.find(hql);
	}
	
	public List<Area> getChildList(String code){
		String hql = "From Area where parentcode = ? and status='"+Constants.STATUS_OPEN+"' ";
		return super.find(hql,code);
	}
	
	public List<Area> getAreaList(String provincecode) {
		String hql = "From Area where provincecode=?  and status= ? ";
		return super.find(hql, provincecode,Constants.STATUS_OPEN);
	}
	
	/**
	 * 通过code 找出对应的 省市区名称
	 * @param code
	 * @return 找出对应的 省市区名称
	 */
	public String getAreaNameByCode(String code){
		String sql = "select name from S_AREA where code ='"+code+"'";
		return getCurrentSession().createSQLQuery(sql).uniqueResult().toString();
	}
	
	/**
	 * 通过名称 查询对应的 code
	 * 仅支持查询  省 和 市  [注：区不在这个方法查询]
	 * @param name
	 * @param flag
	 * @return
	 */
	public String getCodeByName(String name ,String flag){
		String sql = "SELECT code FROM S_AREA WHERE flag ='"+flag+"' AND name ='"+name+"' AND status='"+Constants.STATUS_OPEN+"' ";
		Object result = getCurrentSession().createSQLQuery(sql).uniqueResult();
		if(result!=null){
			return result.toString();
		}
		return null;
	}
	
	/**
	 * 通过名称和市code 查询对应的 区code
	 * @param name	-- 区名称
	 * @param cityCode --市code
	 * @return
	 */
	public String getMCodeByName(String name ,String cityCode){
		String sql = "SELECT code FROM S_AREA WHERE parentcode ='"+cityCode+"' AND name ='"+name+"' AND status='"+Constants.STATUS_OPEN+"' ";
		Object result = getCurrentSession().createSQLQuery(sql).uniqueResult();
		if(result!=null){
			return result.toString();
		}
		return null;
	}
	
	public String getLevel(String code){
		String sql = "select level from S_AREA where code ='"+code+"'";
		Object result = getCurrentSession().createSQLQuery(sql).uniqueResult();
		if(result!=null){
			return result.toString();
		}
		return null;
	}
	
	/**
	 * 
	 * @param orderCode
	 * @return
	 */
	public List<Area> getAreaListByOrderCode(String orderCode){
		String sql = "SELECT * FROM S_AREA sa WHERE "
				+ "EXISTS(SELECT * FROM B_ORDER bo WHERE bo.code=:code "
				+ "AND (bo.province = sa.code OR bo.city = sa.code OR bo.municipality = sa.code)) "
				+ "ORDER BY sa.code";
		
		SQLQuery query = getCurrentSession().createSQLQuery(sql);
    	query.setString("code",orderCode);
    	query.addEntity(Area.class);
    	
    	return query.list();
	
    	
	}
	
}
